﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using Damar.DataSet;
using E4RPTools;

namespace Damar
{
    public class Database
    {
        public static ImportacaoDataSet GetDocuments(SqlTransaction sqlTransaction, DateTime? start, DateTime? end)
        {
            var dataSet = new ImportacaoDataSet();
            const string query =
                @"  select	CDU_TipoDoc, CDU_Id_Expedicao, CDU_NumDoc, CDU_NCliente, CDU_NomeCliente, CDU_MoradaCliente, CDU_CodigoPostalCliente,
	                        CDU_Requisicao, CDU_Moeda, CDU_Cambio, CDU_Data, CDU_Vencimento, CDU_NContribuinteCliente, CDU_DescCi, CDU_CondicaoPagamento,
	                        CDU_DescFin, CDU_Zona, CDU_Vend, CDU_Pag, CDU_Artigo, CDU_Descricao, CDU_Quantidade, CDU_Unidade, CDU_LocalCarga,
	                        CDU_LocalDescarga, CDU_Matricula, CDU_Carga, CDU_Descarga, CDU_ModoExpedicao, CDU_Armazem, CDU_Lote, CDU_DescricaoLote, 
                            CDU_DataFabrico, CDU_Validade, CDU_Controlador, CDU_Observacoes, CDU_DataImportacao, CDU_Id, CDU_NumeroDocumento, 
                            CDU_TipoDocumento, CDU_PCusto, CDU_PrecUnit
		            from	TDU_Importacao";
            var parameters = new List<SqlParameter>();
            var filter = "";
            if(start.HasValue)
            {
                filter += " where CDU_Data > @Start";
                parameters.Add(new SqlParameter("Start", start.Value));
            }
            if (end.HasValue)
            {
                filter += filter != "" ? " and " : " where ";
                filter += " CDU_Data < @End" ;
                parameters.Add(new SqlParameter("End", end.Value.AddDays(1)));
            }
            using (var dataAccess = new SqlDataAccess())
                dataAccess.Fill(sqlTransaction, dataSet.TDU_Importacao, query + filter, parameters.ToArray());
            return dataSet;
        }

        public static ImportacaoDataSet GetDocumentsToImport(SqlTransaction sqlTransaction, DateTime? start, DateTime? end, int documentNumber, string documentType)
        {
            var dataSet = new ImportacaoDataSet();
            const string query =
                @"  select	CDU_TipoDoc, CDU_Id_Expedicao, CDU_NumDoc, CDU_NCliente, CDU_NomeCliente, CDU_MoradaCliente, CDU_CodigoPostalCliente,
	                        CDU_Requisicao, CDU_Moeda, CDU_Cambio, CDU_Data, CDU_Vencimento, CDU_NContribuinteCliente, CDU_DescCi, CDU_CondicaoPagamento,
	                        CDU_DescFin, CDU_Zona, CDU_Vend, CDU_Pag, CDU_Artigo, CDU_Descricao, CDU_Quantidade, CDU_Unidade, CDU_LocalCarga,
	                        CDU_LocalDescarga, CDU_Matricula, CDU_Carga, CDU_Descarga, CDU_ModoExpedicao, CDU_Armazem, CDU_Lote, CDU_DescricaoLote, 
                            CDU_DataFabrico, CDU_Validade, CDU_Controlador, CDU_Observacoes, CDU_DataImportacao, CDU_Id, CDU_NumeroDocumento, 
                            CDU_TipoDocumento, CDU_PCusto, CDU_PrecUnit
		            from	TDU_Importacao
                    where   CDU_NumDoc = @DocumentNumber 
                            and CDU_TipoDoc = @DocumentType
                            and CDU_DataImportacao is null";
            var parameters = new List<SqlParameter>();
            parameters.Add(new SqlParameter("DocumentNumber", documentNumber));
            parameters.Add(new SqlParameter("DocumentType", documentType));
            var filter = "";
            if (start.HasValue)
            {
                filter += " and CDU_Data >= @Start";
                parameters.Add(new SqlParameter("Start", start.Value));
            }
            if (end.HasValue)
            {
                filter += " and CDU_Data < @End";
                parameters.Add(new SqlParameter("End", end.Value.AddDays(1)));
            }
            using (var dataAccess = new SqlDataAccess())
                dataAccess.Fill(sqlTransaction, dataSet.TDU_Importacao, query + filter, parameters.ToArray());
            return dataSet;
        }

        public static ProcessoImportacaoDataSet GetDistinctDocuments(SqlTransaction sqlTransaction, DateTime? start, DateTime? end)
        {
            var dataSet = new ProcessoImportacaoDataSet();
            const string query =
                @"  select	CDU_NumDoc, 
                            max(CDU_Data) as CDU_data, 
                            CDU_TipoDoc
		            from	TDU_Importacao
                    where   CDU_DataImportacao is null";
            var parameters = new List<SqlParameter>();
            var filter = "";
            if(start.HasValue)
            {
                filter += " and CDU_Data >= @Start";
                parameters.Add(new SqlParameter("Start", start.Value));
            }
            if (end.HasValue)
            {
                filter += " and CDU_Data < @End" ;
                parameters.Add(new SqlParameter("End", end.Value.AddDays(1)));
            }
            filter += " group by CDU_NumDoc, CDU_TipoDoc"; 
            using (var dataAccess = new SqlDataAccess())
                dataAccess.Fill(sqlTransaction, dataSet.TDU_Importacao, query + filter, parameters.ToArray());
            return dataSet;
        }

        public static string GetDocumentType(SqlTransaction sqlTransaction, string productionType)
        {
            const string query =
                @"  select  CDU_DocumentoPrimavera
                    from    TDU_TipoDocEquivalencia
                    where   CDU_DocumentoProducao = @DocumentType";
            using (var dataAccess = new SqlDataAccess())
                return dataAccess.ExecuteScalar(sqlTransaction, "", query, new SqlParameter("DocumentType", productionType));
        }

        public static short GetDocumentTypeId(SqlTransaction sqlTransaction, string productionType)
        {
            const string query =
                @"  select  TipoDocumento
                    from    DocumentosStk
                    where   Documento = @DocumentType";
            using (var dataAccess = new SqlDataAccess())
                return dataAccess.ExecuteScalar(sqlTransaction, Convert.ToInt16(-1), query, new SqlParameter("DocumentType", productionType));
        }

        

        public static ArtigosEquivalenciaDataSet GetProducts(SqlTransaction sqlTransaction, string productionProduct, string productionUnit)
        {
            var dataSet = new ArtigosEquivalenciaDataSet();
            const string query =
                @"  select	CDU_ArtigoPrimavera, 
                            CDU_ArtigoProducao, 
                            CDU_UnidadeProducao,
                            CDU_UnidadePrimavera
		            from	TDU_ArtigosEquivalencia
                    where   CDU_ArtigoProducao = @ProductionProduct 
                            and CDU_UnidadeProducao = @ProductionUnit";
            using (var dataAccess = new SqlDataAccess())
                dataAccess.Fill(sqlTransaction, dataSet.TDU_ArtigosEquivalencia, query, 
                    new SqlParameter("ProductionProduct", productionProduct), new SqlParameter("ProductionUnit", productionUnit));
            return dataSet;
        }

        public static void UpdateDocuments(SqlTransaction sqlTransaction, int numDoc, string tipoDoc, DateTime date, long documentNumber, string documentType)
        {
            const string query =
                @"  update  TDU_Importacao
                    set     CDU_NumeroDocumento = @DocumentNumber,
                            CDU_TipoDocumento = @DocumentType,
                            CDU_DataImportacao = GetDate()
                    where   CDU_NumDoc = @NumDoc
                            and CDU_data = @Data
                            and CDU_TipoDoc = @TipoDoc
		                    and CDU_DataImportacao is null";
            var parameters = new List<SqlParameter>
                                 {
                                     new SqlParameter("DocumentNumber", documentNumber),
                                     new SqlParameter("DocumentType", documentType),
                                     new SqlParameter("NumDoc", numDoc),
                                     new SqlParameter("Data", date),
                                     new SqlParameter("TipoDoc", tipoDoc)
                                 };
            using (var dataAccess = new SqlDataAccess())
                dataAccess.ExecuteNonQuery(sqlTransaction, query, parameters.ToArray());

        }


        public static int SavedStockDocumentEntries(SqlTransaction sqlTransaction, string documentType, int documentNumber)
        {
            const string query =
                @"  select  COUNT(*) 
                    from    LinhasSTK 
                    where   TipoDoc = @DocumentType 
                            and NumDoc = @DocumentNumber";
            using (var dataAccess = new SqlDataAccess())
                return dataAccess.ExecuteScalar(sqlTransaction, 0, query,
                    new SqlParameter("DocumentType", documentType), new SqlParameter("DocumentNumber", documentNumber));
        }
    }
}
